package pers.gxb.learn.db.mysql.query;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 百万级数据 查询速度 千万级数据查询速度 上亿条数据查询速度
 * 
 * @author guoxinbo
 *
 */
public class QueryTest
{
    Connection conn = null;
    
    Statement st = null;
    
    private void init()
        throws ClassNotFoundException, SQLException
    {
        initConn();
        st = conn.createStatement();
    }
    
    public void initConn()
        throws ClassNotFoundException, SQLException
    {
        String sql;
        String url = "jdbc:mysql://localhost:3306/learn?" + "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
        try
        {
            Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
            conn = DriverManager.getConnection(url);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    
    public void excute(String sql)
        throws SQLException
    {
        long s = System.currentTimeMillis();
        ResultSet rs = st.executeQuery(sql);
        int i = 0;
        while (rs.next())
        {
            i++;
        }
        long e = System.currentTimeMillis();
        float total = (e - s) / 1000f;
        System.out.println("耗时：" + total + "s" + " ;查询结果 ：" + i + " 个 ; SQL： " + sql);
    }
    
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException
    {
        QueryTest test = new QueryTest();
        test.init();
        // test600wan(test);
        test1yi(test);
    }
    
    private static void test600wan(QueryTest test)
        throws SQLException
    {
        String sql1 = "select * from tb_big_data where  random=2 and count=1 ";
        test.excute(sql1);
        
        String sql2 = "select * from tb_big_data limit 10 ";
        test.excute(sql2);
        
        String sql3 = "SELECT * FROM tb_big_data  ORDER BY random LIMIT 20  ";
        test.excute(sql3);
        
    }
    
    private static void test1yi(QueryTest test)
        throws SQLException
    {
        String sql1 = "select * from tb_yi_data where count=1 ";
        test.excute(sql1);
        
        String sql2 = "select * from tb_yi_data limit 10 ";
        test.excute(sql2);
        
        String sql3 = "SELECT * FROM tb_yi_data  ORDER BY random LIMIT 20  ";
        test.excute(sql3);
    }
}
